Create Staging Tables in Staging Database and Populate the Staging Tables 7

Download the Sql File attached to the                            

Run Script that is attached to create your Staging tables in the Staging Database. Once ran the tables will be made up of these columns.





Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard;

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion
 
string strFileName;    
string strFilePath;      
 
strFilePath = Dts.Variables["User::JSONFILEPATH"].Value.ToString();
strFileName = Path.GetFileName(strFilePath);
 
 Dts.Variables["User::JSONFILENAME"].Value = strFileName;



       


C# Code for Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NYCHRNameSpace

{
    class NYCPayroll
    {
        public string FiscalYear { get; set; }
        public string PayrollNumber { get; set; }
        public string AgencyName { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string MidInit { get; set; }
        public string AgencyStartDate { get; set; }
        public string WorkLocationBorough { get; set; }
        public string TitleDescription { get; set; }
        public string LeaveStatusasofJune30 { get; set; }
        public string BaseSalary { get; set; }
        public string PayBasis { get; set; }
        public string RegularHours { get; set; }
        public string RegularGrossPaid { get; set; }
        public string OTHours { get; set; }
        public string TotalOTPaid { get; set; }
        public string TotalOtherPay { get; set; }

    }
}

?C# Code for Main

#region Namespaces

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Collections.Generic;

using System.Text;

using System.Web.Script.Serialization;

using System.IO;

using NYCHRNameSpace;

#endregion

string readfile = Variables.JSONFILEPATH;

  String jsonFileContent =           File.ReadAllText(readfile);

        JavaScriptSerializer js = new JavaScriptSerializer() { MaxJsonLength = 86753090 };

      List<NYCPayroll> NYCPayrollData = js.Deserialize<List<NYCPayroll>>(jsonFileContent);

       foreach (NYCPayroll DataList in NYCPayrollData)

        { Output0Buffer.AddRow();

            Output0Buffer.FiscalYear = DataList.FiscalYear;

            Output0Buffer.PayrollNumber = DataList.PayrollNumber;

            Output0Buffer.AgencyName = DataList.AgencyName;

            Output0Buffer.LastName = DataList.LastName;

            Output0Buffer.FirstName = DataList.FirstName;

            Output0Buffer.MidInit = DataList.MidInit;

            Output0Buffer.AgencyName = DataList.AgencyName;

            Output0Buffer.AgencyStartDate = DataList.AgencyStartDate;

            Output0Buffer.WorkLocationBorough = DataList.WorkLocationBorough;

            Output0Buffer.TitleDescription = DataList.AgencyName;

            Output0Buffer.LeaveStatusasofJune30 = DataList.LeaveStatusasofJune30;

            Output0Buffer.BaseSalary = DataList.BaseSalary;

            Output0Buffer.PayBasis = DataList.PayBasis;

            Output0Buffer.RegularHours = DataList.RegularHours;

            Output0Buffer.RegularGrossPaid = DataList.RegularGrossPaid;

            Output0Buffer.OTHours = DataList.OTHours;

            Output0Buffer.TotalOTPaid = DataList.TotalOTPaid;

            Output0Buffer.TotalOtherPay = DataList.TotalOtherPay;

        }

 


·       

 Drag and Drop the Derived Column transformation - add the new column Filename and Insert date

·         Drag and drop the Data Conversion transformation - to convert the appropriate Data Types

·         Drag and drop the OLEDB Destination - add the destination connection manager and select the  table to load   the data



DDerived Column Code:


Data Conversion:

Using the Script Component in SSIS

https://mindmajix.com/ssis/using-script-component